package com.nehc.nettyserver.service.impl;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.nehc.nettyserver.common.model.Message;
import com.nehc.nettyserver.service.StorageService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * H2数据库存储服务实现
 * 
 * @author NEHC
 */
@Service
@RequiredArgsConstructor
@Slf4j
public class H2StorageServiceImpl implements StorageService {

    private final JdbcTemplate jdbcTemplate;
    private final ObjectMapper objectMapper = new ObjectMapper();
    
    private final RowMapper<Message> messageRowMapper = (rs, rowNum) -> {
        try {
            Message message = new Message();
            message.setId(rs.getString("id"));
            message.setType(rs.getString("type"));
            message.setSenderId(rs.getString("sender_id"));
            message.setReceiverId(rs.getString("receiver_id"));
            message.setTimestamp(rs.getLong("create_time"));
            
            // 解析payload
            String payloadJson = rs.getString("payload");
            if (payloadJson != null && !payloadJson.isEmpty()) {
                Map<String, Object> payload = objectMapper.readValue(payloadJson, Map.class);
                message.setPayload(payload);
            }
            
            return message;
        } catch (Exception e) {
            log.error("Failed to map message from database", e);
            return null;
        }
    };

    @Override
    public boolean saveMessage(Message message) {
        try {
            // 序列化payload
            String payloadJson = message.getPayload() != null 
                ? objectMapper.writeValueAsString(message.getPayload()) 
                : null;
            
            String sql = "INSERT INTO sys_message (id, type, sender_id, receiver_id, content, payload, read_status, create_time) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            
            int rows = jdbcTemplate.update(sql,
                    message.getId(),
                    message.getType(),
                    message.getSenderId(),
                    message.getReceiverId(),
                    message.getPayload() != null && message.getPayload().containsKey("content") ? message.getPayload().get("content").toString() : null,
                    payloadJson,
                    0, // 默认未读
                    message.getTimestamp());
            
            return rows > 0;
        } catch (Exception e) {
            log.error("Failed to save message: {}", message.getId(), e);
            return false;
        }
    }

    @Override
    public Message getMessage(String messageId) {
        try {
            String sql = "SELECT * FROM sys_message WHERE id = ? AND deleted = 0";
            List<Message> messages = jdbcTemplate.query(sql, messageRowMapper, messageId);
            return messages.isEmpty() ? null : messages.get(0);
        } catch (Exception e) {
            log.error("Failed to get message: {}", messageId, e);
            return null;
        }
    }

    @Override
    public List<Message> getUserMessages(String userId, int limit) {
        return getUserMessages(userId, 0, limit);
    }

    @Override
    public List<Message> getUserMessages(String userId, int offset, int limit) {
        try {
            String sql = "SELECT * FROM sys_message WHERE (sender_id = ? OR receiver_id = ?) AND deleted = 0 "
                    + "ORDER BY create_time DESC LIMIT ? OFFSET ?";
            return jdbcTemplate.query(sql, messageRowMapper, userId, userId, limit, offset);
        } catch (Exception e) {
            log.error("Failed to get user messages for user: {}", userId, e);
            return List.of();
        }
    }

    @Override
    public List<Message> getClientMessages(String clientId, int offset, int limit) {
        try {
            String sql = "SELECT * FROM sys_message WHERE (sender_id = ? OR receiver_id = ?) AND deleted = 0 "
                    + "ORDER BY create_time DESC LIMIT ? OFFSET ?";
            return jdbcTemplate.query(sql, messageRowMapper, clientId, clientId, limit, offset);
        } catch (Exception e) {
            log.error("Failed to get client messages for client: {}", clientId, e);
            return List.of();
        }
    }

    @Override
    public boolean saveConnection(String connectionId, Object data) {
        try {
            String dataJson = objectMapper.writeValueAsString(data);
            String sql = "INSERT INTO sys_connection (id, client_id, user_id, ip_address, connect_time, client_info) "
                    + "VALUES (?, ?, ?, ?, ?, ?)";
            
            Map<String, Object> connectionData = (Map<String, Object>) data;
            int rows = jdbcTemplate.update(sql,
                    connectionId,
                    connectionData.get("clientId"),
                    connectionData.get("userId"),
                    connectionData.get("ipAddress"),
                    connectionData.get("connectTime"),
                    dataJson);
            
            return rows > 0;
        } catch (Exception e) {
            log.error("Failed to save connection: {}", connectionId, e);
            return false;
        }
    }

    @Override
    public boolean updateConnection(String connectionId, Object data) {
        try {
            String dataJson = objectMapper.writeValueAsString(data);
            String sql = "UPDATE sys_connection SET client_info = ?, disconnect_time = ?, duration = ?, "
                    + "disconnect_reason = ? WHERE id = ?";
            
            Map<String, Object> connectionData = (Map<String, Object>) data;
            int rows = jdbcTemplate.update(sql,
                    dataJson,
                    connectionData.get("disconnectTime"),
                    connectionData.get("duration"),
                    connectionData.get("disconnectReason"),
                    connectionId);
            
            return rows > 0;
        } catch (Exception e) {
            log.error("Failed to update connection: {}", connectionId, e);
            return false;
        }
    }

    @Override
    public Object getConnection(String connectionId) {
        try {
            String sql = "SELECT * FROM sys_connection WHERE id = ?";
            List<Map<String, Object>> results = jdbcTemplate.queryForList(sql, connectionId);
            
            if (results.isEmpty()) {
                return null;
            }
            
            Map<String, Object> result = results.get(0);
            String clientInfoJson = (String) result.get("client_info");
            
            Map<String, Object> connectionData = new HashMap<>();
            connectionData.put("id", result.get("id"));
            connectionData.put("clientId", result.get("client_id"));
            connectionData.put("userId", result.get("user_id"));
            connectionData.put("ipAddress", result.get("ip_address"));
            connectionData.put("connectTime", result.get("connect_time"));
            connectionData.put("disconnectTime", result.get("disconnect_time"));
            connectionData.put("duration", result.get("duration"));
            connectionData.put("disconnectReason", result.get("disconnect_reason"));
            
            if (clientInfoJson != null && !clientInfoJson.isEmpty()) {
                Map<String, Object> clientInfo = objectMapper.readValue(clientInfoJson, Map.class);
                connectionData.putAll(clientInfo);
            }
            
            return connectionData;
        } catch (Exception e) {
            log.error("Failed to get connection: {}", connectionId, e);
            return null;
        }
    }

    @Override
    public int getMessageCount(long startTime, long endTime) {
        try {
            String sql = "SELECT COUNT(*) FROM sys_message WHERE create_time BETWEEN ? AND ? AND deleted = 0";
            return jdbcTemplate.queryForObject(sql, Integer.class, startTime, endTime);
        } catch (Exception e) {
            log.error("Failed to get message count", e);
            return 0;
        }
    }

    @Override
    public List<Object[]> getMessageCountByType(long startTime, long endTime) {
        try {
            String sql = "SELECT type, COUNT(*) FROM sys_message WHERE create_time BETWEEN ? AND ? AND deleted = 0 GROUP BY type";
            return jdbcTemplate.query(sql, (rs, rowNum) -> new Object[] {
                rs.getString(1), rs.getInt(2)
            }, startTime, endTime);
        } catch (Exception e) {
            log.error("Failed to get message count by type", e);
            return List.of();
        }
    }

    @Override
    public List<Object[]> getMessageCountByHour(long startTime, long endTime) {
        try {
            // H2不支持复杂的时间函数，这里使用简化版本
            String sql = "SELECT CAST(create_time / 3600000 AS INT) * 3600000 AS hour, COUNT(*) " +
                    "FROM sys_message WHERE create_time BETWEEN ? AND ? AND deleted = 0 " +
                    "GROUP BY CAST(create_time / 3600000 AS INT) ORDER BY hour";
            
            return jdbcTemplate.query(sql, (rs, rowNum) -> new Object[] {
                rs.getLong(1), rs.getInt(2)
            }, startTime, endTime);
        } catch (Exception e) {
            log.error("Failed to get message count by hour", e);
            return List.of();
        }
    }
} 